IF EXISTS (SELECT * FROM sysobjects WHERE name = 'PurgeEventLog' AND type = 'P')
     DROP PROCEDURE dbo.PurgeEventLog
GO

CREATE PROCEDURE dbo.PurgeEventLog
AS
/*
 *
 * PurgeEventLog
 *
 * Purpose:      To remove old entries in the EventLog table
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      Records may be deleted.
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: MaxEventLogDaysToKeep, number of days to keep records, 
 *               MaxEventLogIdCollectedByRepos, highest EventLogId that was
 *               collected by the repository collection process.
 *
 *               Record delete be prevented if the MaxEventLogIdCollectedByRepos
 *               is less than the date range that is to be deleted.  this value 
 *               does not have any effect on the collection process, it exists 
 *               to assist the purge process decide whether it should delete 
 *               records.  The main purpose is to prevent record deletion before
 *               they can be collected.
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 06/11/2009, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     EXEC dbo.InsEventLog @EventLogTypeId     = 1, -- Info
                          @LogByApp           = 'PurgeEventLog',
                          @LogText            = 'Process Starting'

     DECLARE @MaxEventLogDaysToKeep            bigint,
             @RepositoryInUseFlag              tinyint,
             @RepositoryInUseFlagStr           varchar(8000),
             @MaxEventLogIdCollectedByRepos    bigint,
             @MaxEventLogIdForDt               bigint,
             @BeforeEventLogId                 bigint,
             @DeleteBeforeDt                   datetime,
             @msg                              varchar(6144),
             @RC                               bigint

     -- Get stored paramters, but don't use the normal functions, read directly.
     SELECT @MaxEventLogDaysToKeep = CONVERT( int, CfgCtrlValue )
     FROM   dbo.CfgCtrl
     WHERE  CfgCtrlKey = 'MaxEventLogDaysToKeep'

     SELECT @MaxEventLogIdCollectedByRepos = CONVERT( bigint, CfgCtrlValue )
     FROM   dbo.CfgCtrl
     WHERE  CfgCtrlKey = 'MaxEventLogIdCollectedByRepos'

     SELECT @RepositoryInUseFlagStr = CfgCtrlValue 
     FROM   dbo.CfgCtrl
     WHERE  CfgCtrlKey = 'RepositoryInUseFlag'

     IF ( @RepositoryInUseFlagStr = '1' )
     BEGIN
          SET @RepositoryInUseFlag = 1
     END
     ELSE
     BEGIN
          SET @RepositoryInUseFlag = 0
     END

     -- Figure out what date we are working with..
     SELECT @DeleteBeforeDt = DATEADD( dd, -1 * ISNULL( @MaxEventLogDaysToKeep, 30 ), GETDATE() )

     -- Figure out what EventLogId that date that date range covers.
     SELECT @MaxEventLogIdForDt = MAX(EventLogId) 
     FROM   dbo.EventLog 
     WHERE  LogDt <= @DeleteBeforeDt

     -- Determine if we need to handle the Collection process preventing deletes
     IF ( @RepositoryInUseFlag = 1 )
     BEGIN -- Repository in use
          IF ( @MaxEventLogIdForDt <= @MaxEventLogIdCollectedByRepos )
          BEGIN -- The records in range for the date are less than what has been
                -- collected.
               SELECT @BeforeEventLogId = @MaxEventLogIdForDt
          END
          ELSE
          BEGIN -- The records in range for the date are above what has been 
                -- collected, so we'll find out what we can delete.
               -- Get the highest id that is in the date range we want AN below
               -- what has been picked up by the collection process.
               SELECT @BeforeEventLogId = MAX(EventLogId)
               FROM   dbo.EventLog 
               WHERE  EventLogId <= @MaxEventLogIdCollectedByRepos
               AND    LogDt      <= @DeleteBeforeDt
          END
     END
     ELSE
     BEGIN -- Repository NOT in use
          SET @BeforeEventLogId = @MaxEventLogIdForDt
     END

     -- Delete the records that are in scope, keeping in mind what has or has
     -- not been collected by the collection process..
     DELETE 
     FROM   dbo.EventLog
     WHERE  EventLogId <= @BeforeEventLogId 

     SELECT @RC = @@ROWCOUNT

     SELECT @msg = 'Process Ending.  @DeleteBeforeDt in MiscDt1, '
                 + '@MaxEventLogIdForDt in MiscBigInt1, '
                 + '@MaxEventLogIdCollectedByRepos in MiscBigInt2, '
                 + '@BeforeEventLogId in MiscBigInt3, '
                 + 'Records deleted count in MiscBigInt4, '
                 + '@RepositoryInUseFlag in MiscTinyInt1.'

     EXEC dbo.InsEventLog @EventLogTypeId     = 1, -- Info
                          @LogByApp           = 'PurgeEventLog',
                          @LogText            = @msg,
                          @MiscDt1            = @DeleteBeforeDt,
                          @MiscBigInt1        = @MaxEventLogIdForDt,
                          @MiscBigInt2        = @MaxEventLogIdCollectedByRepos,
                          @MiscBigInt3        = @BeforeEventLogId,
                          @MiscBigInt4        = @RC,
                          @MiscTinyInt1       = @RepositoryInUseFlag
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Purges records out of the eventlog table after they have been held for the required period of time.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = PurgeEventLog;
GO
